multiple conditions function AVERAGEIFS, COUNTIFS and SUMIFS to our work has brought great convenience, as in the year a total score of the table in paragraph subjects the average calculated each class, pass rates and excellent rates, do not have to first sort by class, and then calculate the average placement rate of the points and two. Realization method is as follows:

1, the first in the "certification card," this column blank cell below the cell type classes, and then to the "Name" column below the average input, respectively, pass rates and excellent rates.

2, in the "language" This column of cells below the "average" cell corresponding input = AVERAGEIFS (C2: C132, $ G $ 2: $ G $ 132, "= 1"). (Figure 1)

AVERAGEIFS multiple conditions is calculated using the specified range of the average cell function. The syntax is: AVERAGEIFS (average_range, criteria_range1, criteria1, criteria_range2, criteria2 ...), Average_range is to calculate the average range of cells, including the number or names that contain numbers, arrays, or references; Criteria_range1, criteria_range2, ... is What are the conditions specified in the range of cells; Criteria1, criteria2, ... refers to what the specific conditions.

Average_range formula is C2: c132, the "language" that with all the results. Criteria_range1 is G2: G132, the class that the column data, before and after G $ is added to the next can drag the fill additional mathematics and English subjects ($ as the absolute application, drag the formula cell applications cited does not change). Criteria1 is "= 1", that the conditions for the class is the class.

3, in the "language" This column of cells below the "pass rate" corresponding to the cell type = COUNTIFS (C2: C132, "> = 90", $ G $ 2: $ G $ 132, "= 1" ) / COUNTIF ($ G $ 2: $ G $ 132, "= 1"). (Figure 2)

COUNTIFS statistical conditions for a region to meet the multiple number of cells. The syntax is: COUNTIFS (range1, criteria1, range2, criteria2 ...), Range1, range2, ... refers to the time of enumeration areas which impose conditions. Criteria1, criteria2, ... is what the specific conditions. Range1 formula is C2: C132, the "language" that out scores; Criteria1 is "> = 90", that condition score greater than or equal 90; range2 is G2: G132, that is, "class" this column data , additional $ also for convenience of others dragged fills discipline; criteria2 refers "= 1" that conditions classes is class.

COUNTIF function is a statistical designated area to meet the given conditions a function of the number of cells. The syntax is: COUNTIF (range, criteria), range is one or more cells to count, including the number or names, arrays, or references that contain numbers, null values and text values are ignored. criteria is the statistics on what conditions, the form of a number, expression, cell reference or text. Formula range is G2: G132, that is, "class" which lists all the cells; criteria is "= 1", meaning that conditions for the class is a group of students.

4, the passing rate calculation method in accordance with the statistics good rate, that is 90 to 120 (out of 150, 120 points or more for the good).

5, select the pass rate and excellent rates of the two lines, in the "Start" tab under the "digital" control group, click "%" (percent style button) into percentage display. You can also click the ensuing "increase decimal places" and "reduce the number of decimal places" button to increase or decrease decimal places. (Figure 3)

In addition, we can also use the scores COUNTIFS to statistics the number of segments, such as statistical language scores assigned 130 points among the 120 number, enter: = COUNTIFS (C2: C132, "> = 120", C2: C132, "<130")